# -*- coding: utf-8 -*-
"""
Created on Fri Mar 15 09:56:58 2019

@author: AINIVERSHERRY
"""

import numpy as np
import pandas as pd
import warnings
warnings.filterwarnings('ignore')  

data = pd.read_csv('G:/Software_Files/Python_Files/LoanStats_2018Q1.csv',
                   sep = ',')
data.info()

#预处理，查看缺失值情况
data.head()
data.tail()
check_null = data.isnull().sum().sort_values(ascending = False) / float(len(data))

#计数并输出缺失值比例高于50%的变量
count = 0
for x in check_null:
    if x > 0.5:
        count += 1        
print(count)
print(check_null[check_null > 0.5])

#删掉缺失值高于XX%的变量
data1 = data.dropna(how = 'all', axis = 1)
thresh_set = len(data) * 0.5
data2 = data.dropna(axis = 1, thresh = thresh_set)
data2[['annual_inc_joint', 'dti_joint', 'mths_since_recent_bc_dlq',\
       'mths_since_recent_revol_delinq', 'mths_since_last_delinq',\
       'settlement_amount']] = data1[['annual_inc_joint', 'dti_joint',\
       'mths_since_recent_bc_dlq', 'mths_since_recent_revol_delinq',\
       'mths_since_last_delinq', 'settlement_amount']]

#处理同值变量 
data2.dtypes.value_counts()
data3 = data2.loc[:, data2.nunique() != 1]

#查看分类型变量的缺失情况及调整
object_columns = data3.select_dtypes(include = ['object']).columns

count = 0
for x in object_columns:
    print(x)
    print(data3[x].head())
    count += 1
print()
print('分类型变量总数为：%d'%count)

#发现存在分类型变量实际应当是数值型变量，数据类型重分类
data3['int_rate'] = data3['int_rate'].str.rstrip('%').astype('float')
data3['revol_util'] = data3['revol_util'].str.rstrip('%').astype('float')
object_columns = data3.select_dtypes(include = ['object']).columns
 
#分类型变量缺失值可视化
import missingno as msno
msno.matrix(data3[object_columns])
msno.heatmap(data3[object_columns])  #查看缺失值之间的相关性

#对分类型变量的剩余缺失值做填充
data3[object_columns] = data3[object_columns].fillna('Unknown')
msno.bar(data3[object_columns])  #确认填充完毕

#查看数值型变量的缺失情况及调整
numeric_columns = data3.select_dtypes(include = ['float64']).columns

#查看前后五行
data3[numeric_columns].head()
data3[numeric_columns].tail()

#缺失值可视化
msno.bar(data3[numeric_columns])

#均值插补
from sklearn.preprocessing import Imputer
imr = Imputer(missing_values = 'NaN', strategy = 'mean', axis = 0)  # axis = 0针对列
imr = imr.fit(data3[numeric_columns])
data3[numeric_columns] = imr.transform(data3[numeric_columns])

msno.bar(data2[numeric_columns])

#数据过滤，将重复或对构建预测模型没有意义的属性进行删除
drop_list = ['sub_grade', 'emp_title',  'title', 'zip_code', 'addr_state', \
             'initial_list_status','issue_d','last_pymnt_d', 'last_pymnt_amnt',\
             'next_pymnt_d','last_credit_pull_d', 'earliest_cr_line']
data4 = data3.drop(drop_list, axis = 1)

data4.to_csv('loans_2018q1_pre.csv',  index = False)

